using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using Fieldbook.Entities;
using Fieldbook.Common;
namespace Fieldbook.DAL
{
	public static class HouseholdDAO
	{
		#region Insert
		private static DbCommand insertCommand = null;
		private static string insertSQL = "INSERT INTO dbo.Household (HEADID, MEMBERID) VALUES (@HEADID, @MEMBERID) ";
		private static DbCommand GetInsertCommand(SqlDatabase db)
		{
			if (insertCommand == null)
			{
				insertCommand = db.GetSqlStringCommand(insertSQL);
				db.AddInParameter(insertCommand, "@HEADID", DbType.Int64);
				db.AddInParameter(insertCommand, "@MEMBERID", DbType.Int64);
			}
			return insertCommand;
		}
		private static void SetInsertValues(SqlDatabase db, DbCommand cmd, Household entity)
		{
			db.SetParameterValue(cmd, "@HEADID", entity.Headid);
			db.SetParameterValue(cmd, "@MEMBERID", entity.Memberid);
		}
		public static bool InsertHousehold(Household entity)
		{
			bool res = false;
			SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
			DbCommand cmd = GetInsertCommand(db);
			SetInsertValues(db, cmd, entity);
			res = Convert.ToBoolean(db.ExecuteNonQuery(cmd));
			
			return res;
		}
		public static bool InsertHousehold(Household entity, DbTransaction transactionObj, SqlDatabase db)
		{
			bool res = false;
			DbCommand cmd = GetInsertCommand(db);
			SetInsertValues(db, cmd, entity);
			res = Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
			
			return res;
		}
		public static bool InsertListHousehold(List<Household> list)
		{
			bool res = true;
			DbTransaction transactionObj = null;
			DbConnection conn = null;
			try
			{
				SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
				DbCommand cmd = GetInsertCommand(db);
				conn = db.CreateConnection();
				conn.Open();
				transactionObj = conn.BeginTransaction();
				for(int i=0; i<list.Count; i++)
				{
					Household entity = list[i];
					SetInsertValues(db, cmd, entity);
					res = res & Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
					
				}
				transactionObj.Commit();
				transactionObj.Dispose();
				conn.Close();
				conn.Dispose();
			}
			catch (Exception ex)
			{
				res = false;
				if (transactionObj != null)
				{
					transactionObj.Rollback();
					transactionObj.Dispose();
				}
				if (conn != null & conn.State != ConnectionState.Closed)
				{
					conn.Close();
					conn.Dispose();
				}
				throw ex;
			}
			return res;
		}
		public static bool InsertListHousehold(List<Household> list, DbTransaction transactionObj, SqlDatabase db)
		{
			bool res = true;
			DbCommand cmd = GetInsertCommand(db);
			for(int i=0; i<list.Count; i++)
			{
				Household entity = list[i];
				SetInsertValues(db, cmd, entity);
				res = res & Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
				
			}
			return res;
		}
		#endregion
		
		
		#region Delete
		private static DbCommand GetDeleteCommand(SqlDatabase db)
		{
            string deleteSQL = "DELETE FROM dbo.Household WHERE HEADID = @HEADID AND MEMBERID = @MEMBERID";
            DbCommand deleteCommand = db.GetSqlStringCommand(deleteSQL);
            db.AddInParameter(deleteCommand, "@HEADID", DbType.Int64);
            db.AddInParameter(deleteCommand, "@MEMBERID", DbType.Int64);
			return deleteCommand;
		}

		private static void SetDeleteValues(SqlDatabase db, DbCommand cmd, Household entity)
		{
			db.SetParameterValue(cmd, "@HEADID", entity.Headid);
			db.SetParameterValue(cmd, "@MEMBERID", entity.Memberid);
		}

		public static bool DeleteHousehold(Household entity)
		{
			bool res = false;
			SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
			DbCommand cmd = GetDeleteCommand(db);
			SetDeleteValues(db, cmd, entity);
			res = Convert.ToBoolean(db.ExecuteNonQuery(cmd));
			return res;
		}

        public static bool DeleteHousehold(long headID)
        {
            bool res = false;
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
            string deleteSQL = "DELETE FROM dbo.Household WHERE HEADID = @HEADID";
            DbCommand deleteCommand = db.GetSqlStringCommand(deleteSQL);
            db.AddInParameter(deleteCommand, "@HEADID", DbType.Int64, headID);
            res = Convert.ToBoolean(db.ExecuteNonQuery(deleteCommand));
            return res;
        }

		public static bool DeleteListHousehold(List<Household> list)
		{
			bool res = true;
			DbTransaction transactionObj = null;
			DbConnection conn = null;
			try
			{
				SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
				DbCommand cmd = GetDeleteCommand(db);
				conn = db.CreateConnection();
				conn.Open();
				transactionObj = conn.BeginTransaction();
				for(int i=0; i<list.Count; i++)
				{
					Household entity = list[i];
					SetDeleteValues(db, cmd, entity);
					res = res & Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
				}
				transactionObj.Commit();
				transactionObj.Dispose();
				conn.Close();
				conn.Dispose();
			}
			catch (Exception ex)
			{
				res = false;
				if (transactionObj != null)
				{
					transactionObj.Rollback();
					transactionObj.Dispose();
				}
				if (conn != null & conn.State != ConnectionState.Closed)
				{
					conn.Close();
					conn.Dispose();
				}
				throw ex;
			}
			return res;
		}
		

		#endregion
		#region Select
		public static List<Household> LoadHouseholds(string where, string orderby)
		{
			List<Household> res = new List<Household>();
            string sql = String.Format("SELECT HEADID,MEMBERID FROM dbo.Household {0} {1}", where, orderby);
			IDataReader reader = null;
			try
			{
				SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
				reader = db.ExecuteReader(CommandType.Text, sql);
				if (reader != null)
				{
					while (reader.Read())
					{
						Household entity = new Household();
						entity.Headid = reader.IsDBNull(0) ? null : (long?)reader.GetInt64(0);
						entity.Memberid = reader.IsDBNull(1) ? null : (long?)reader.GetInt64(1);
						res.Add(entity);
					}
					reader.Close();
				}
			}
			catch (Exception ex)
			{
				if (reader != null && !reader.IsClosed)
				{
					reader.Close();
				}
				res = null;
				throw ex;
			}
			return res;
		}

        public static long? GetHouseHoldID(long userID)
        {
            string selectSQL = "SELECT HEADID FROM dbo.Household WHERE MEMBERID = @MEMBERID";
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
            DbCommand cmd = db.GetSqlStringCommand(selectSQL);
            db.AddInParameter(cmd, "@MEMBERID", DbType.Int64, userID);
            return (long?)db.ExecuteScalar(cmd);
        }
		#endregion
	}
}
